package day03;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import util.DBUtil;

public class day03hw {

	
	@Test
	public void test1(){
		Connection conn=null;
		
		try {
			conn=DBUtil.getConnection();
			conn.setAutoCommit(false);
			String sql="insert into emps_pw values("
					+ "emps_seq_pw.nextval,"
					+ "?,?,?,?,?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=1;i<=206;i++){
				ps.setString(1, "唐僧"+i);
				ps.setString(2, "取经");
				ps.setInt(3, 1);
				ps.setDate(4, new Date(System.currentTimeMillis()));
				ps.setDouble(5, 8000.0);
				ps.setDouble(6, 3000.0);
				ps.setInt(7, 20);
				
				ps.addBatch();
				
				if(i%50==0){
					ps.executeBatch();
					ps.clearBatch();
				}
				
			}
			
			ps.executeBatch();
			
			conn.commit();
			System.out.println("增加员工成功！");
		} catch (SQLException e) {
			e.printStackTrace();
			DBUtil.rollBack(conn);
			
		}finally{
			DBUtil.close(conn);
		}
		
	}
	
	
	@Test
	public void test2(){
		//增加的部门、地址
		String dname="经营部";
		String loc="苏州";
		//增加员工信息
		String ename="沙僧";
		String job="挑担子";
		Integer mgr=1;
		Date hiredate=new Date(System.currentTimeMillis());
		Double sal=5000.0;
		Double comm=1000.0;
		
		Connection conn=null;
		try {
			conn=DBUtil.getConnection();
			String sql="insert into depts_pw values("
					+ "depts_seq_pw.nextval,?,?)";
			PreparedStatement ps1 = 
				conn.prepareStatement(sql,new String[]{"deptno"});
			ps1.setString(1,dname);
			ps1.setString(2,loc);
			ps1.executeUpdate();
			System.out.println("部门增加成功！");
			
			ResultSet rs = ps1.getGeneratedKeys();
			rs.next();
			int deptno = rs.getInt(1);
			
			String sql2="insert into emps_pw values("
					+ "emps_seq_pw.nextval,"
					+ "?,?,?,?,?,?,?)";
			PreparedStatement ps2 = 
					conn.prepareStatement(sql2);
			ps2.setString(1, ename);
			ps2.setString(2, job);
			ps2.setInt(3, mgr);
			ps2.setDate(4, hiredate);
			ps2.setDouble(5, sal);
			ps2.setDouble(6, comm);
			ps2.setInt(7, deptno);
			
			ps2.executeUpdate();
			System.out.println("员工增加成功！");
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			DBUtil.close(conn);
		}
		
		
		
		
		
		
		
	}
	
	
	@Test
	public void test3(){
		int page=5;
		int size=10;
		
		
		Connection conn=null;
		
		try {
			conn=DBUtil.getConnection();
			String sql="select * from("
					+ "		select e.*,rownum r from("
					+ "			select*from emps_pw "
					+ " 	 		order by empno) e )"
					+ "	where r between ? and ?" ;
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setInt(1, (page-1)*size+1);
			ps.setInt(2, page*size);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				
				System.out.println(rs.getString("ename")+","+
						rs.getInt("empno")+","+
						rs.getString("job"));
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(conn);
		}
		
	}
	
	
	
	
}
